from itemadapter import ItemAdapter
from scrapy.pipelines.images import ImagesPipeline

# 取出配置文件
from scrapy.utils.project import get_project_settings
import scrapy
from scrapy import item
from scrapy.exceptions import DropItem
import copy
from pymysql import cursors
from twisted.enterprise import adbapi
import time

# orm
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from Models.company import Company

images_store = get_project_settings().get('IMAGES_STORE')

"""
重写图片下载方法
"""
class ImageDownload(ImagesPipeline):

    # 下载图片
    def get_media_requests(self, item, info):
        """
        根据图片的url地址，构造request请求
        :param item:
        :param info:
        :return:
        """
        if len(item['images']) > 0:
            item['images'].pop()

        for image in item['images']:
            yield scrapy.Request(image)

    # 图片下载回调
    def item_completed(self, results, item, info):
        path = [result['path'] for status, result in results if status]
        images = []

        if len(path) > 0:
            for paths in results:
                images.append(paths[1]['path'])
        else:
            print('没有获取到图片(download_images)')

        item['download_images'] = images

        # 获取图片地址，交给下一个管道处理
        return item


"""
重写图片下载方法
"""
class IntroductionImagesDownload(ImagesPipeline):
    # 下载图片
    def get_media_requests(self, item, info):
        """
        根据图片的url地址，构造requests请求
        :param item:
        :param info:
        :return:
        """
        for introduction_image in item['introduction_images']:
            yield scrapy.Request(introduction_image)

    # 图片下载回调
    def item_completed(self, results, item, info):
        path = [result['path'] for status, result in results if status]
        images = []

        if len(path) > 0:
            for paths in results:
                images.append(paths[1]['path'])
        else:
            print('没有获取到图片(download_introduction_images)')

        item['download_introduction_images'] = images
        # 获取图片地址，交给下一个管道处理
        return item


"""
存入数据库
"""
class PeoplePipeline:

    # 初始化函数
    def __init__(self, db_pool, DB):
        # 加入self，写起来好丑啊，感觉一点都不优雅
        self.db_pool = db_pool
        self.DB = DB
        self.date_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        self.insert_sql = "INSERT INTO classified (classified, parent_id, created_time, updated_time) VALUES ('{}', '{}', '{}', '{}')"
        self.select_sql = "SELECT * FROM classified WHERE classified = '{}' "
        self.select_company_sql = "SELECT * FROM company WHERE company = '{}'"
        self.parent_id = 0

    # 从settings配置文件中读取参数
    @classmethod
    def from_settings(cls, settings):
        # 用一个db_params接收连接数据库的参数
        db_params = dict(
            host = settings['MYSQL_HOST'],
            user = settings['MYSQL_USER'],
            password = settings['MYSQL_PASSWORD'],
            port = settings['MYSQL_PORT'],
            database = settings['MYSQL_DBNAME'],
            charset = settings['MYSQL_CHARSET'],
            use_unicode = True,
            # 设置游标类型
            cursorclass = cursors.DictCursor
        )

        # 创建连接池
        db_pool = adbapi.ConnectionPool('pymysql', **db_params)

        # orm
        engine = create_engine('mysql+pymysql://root:root@localhost:3306/people?charset=utf8')
        Company.metadata.create_all(engine)
        Session = sessionmaker(bind = engine)
        DB = Session()

        # 返回一个pipeline对象
        return cls(db_pool, DB)

    # 处理item函数
    def process_item(self, item, spider):
        # 对象拷贝，深拷贝  --- 这里是解决数据重复问题！！！
        asyncItem = copy.deepcopy(item)
        # 把要执行的sql放入连接池
        query = self.db_pool.runInteraction(self.insert_into, asyncItem)
        # 如果sql执行发送错误,自动回调addErrBack()函数
        query.addErrback(self.handle_error, item, spider)
        # 返回Item
        return item

    # 处理sql函数,创建分类
    def insert_into(self, cursor, item):
        """
        保存分类
        :param cursor:
        :param item:
        :return:
        """
        # 他妈的写的真丑啊，这个不是我写的QAQ,过滤城市
        main_category = item['main_category']
        main = []
        city_code = 0
        cat_id = 0

        for type in main_category:
            city = type.replace(" ", "")[0:2]
            cat = type[3:].replace(" ", "")
            cursor.execute("SELECT * FROM city WHERE name LIKE '{}%' LIMIT 1".format(city));
            category = cursor.fetchone()

            if category != None:
                city = type.replace(" ", "")[0:3]
                cat = type[4:].replace(" ", "")
                cursor.execute("SELECT * FROM city WHERE name LIKE '{}%' LIMIT 1".format(city));
                category_second = cursor.fetchone()

                if category_second != None:
                    main.append(type[4:].replace(" ", ""))
                    city_code = category_second['code']
                else:
                    main.append(type[3:].replace(" ", ""))
                    city_code = category['code']

        # 获取一级分类and二级分类
        if len(main) != 0:
            # 查询一级分类是否存在
            cursor.execute(self.select_sql.format(main[0]))
            first_cat_db = cursor.fetchone()

            if first_cat_db == None:
                # 新增一条
                cursor.execute(self.insert_sql.format(main[0], 0, self.date_time, self.date_time))
                # 自增id
                p_id = cursor.lastrowid
                cat_id = cursor.lastrowid

                # 查看二级是否存在
                if len(main) == 2:
                    cursor.execute(self.insert_sql.format(main[1], p_id, self.date_time, self.date_time))
                    cat_id = cursor.lastrowid
            else:
                # 二级存在
                if len(main) == 2:
                    # 查询二级是否被添加
                    cursor.execute(self.select_sql.format(main[1]))
                    second_cat_db = cursor.fetchone()

                    if second_cat_db is None:
                        cursor.execute(self.insert_sql.format(main[1], first_cat_db['id'], self.date_time, self.date_time))
                        cat_id = cursor.lastrowid
                    else:
                        cat_id = first_cat_db['id']

        """
        保存发布人
        """
        cursor.execute("SELECT * FROM company WHERE company = '{}'".format(item['company']))
        company = cursor.fetchone()

        company_id = 0
        # insert
        insert = "INSERT INTO  company (`city_code`,  `cat_id`, `company`,  `is_company`, `contact_person`, `member`, `phone`, `created_time`, `updated_time`) VALUES ('{}', '{}','{}', '{}', '{}', '{}', '{}', '{}', '{}')"
        if company == None:
            cursor.execute(insert.format(
                city_code,
                cat_id,
                item['company'],
                item['is_company'],
                item['contact_person'],
                item['member_tag'][0],
                item['phone'],
                self.date_time,
                self.date_time
            ))
            company_id = cursor.lastrowid
        else:
            company_id = company['id']

        """
        保存服务
        """
        cursor.execute("SELECT * FROM services WHERE merchant_service = '{}'".format(item['merchant_service']))
        service = cursor.fetchone()
        # insert
        insert_service = "INSERT INTO services (`parent_id`, `area`, `merchant_service`, `average_price`, `images`, `service_content`, `service_address`, `introduction`, `introduction_images`, `link`, `created_time`, `updated_time`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        if service is None:
            cursor.execute(
                insert_service, (company_id,
                    item['area'],
                    item['merchant_service'],
                    item['average_price'],
                    ",".join(str(i) for i in item['download_images']),
                    item['service_content'],
                    item['service_address'],
                    item['introduction'],
                    ",".join(str(i) for i in item['download_introduction_images']),
                    item['link'],
                    self.date_time,
                    self.date_time))
        else:
            print("重复数据: " + item['merchant_service'])

        return item

    # 错误函数
    def handle_error(self, failure, item, spider):
        # #输出错误信息
        print("failure", failure)
